<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MySQL优化</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">


html, body, div, span, applet, object, iframe, h1, h2, h3, h4, h5, h6, p, blockquote, pre, a, abbr, acronym, address, big, cite, code, del, dfn, em, img, ins, kbd, q, s, samp, small, strike, strong, sub, sup, tt, var, b, u, i, center, dl, dt, dd, ol, ul, li, fieldset, form, label, legend, table, caption, tbody, tfoot, thead, tr, th, td, article, aside, canvas, details, embed, figure, figcaption, footer, header, hgroup, menu, nav, output, ruby, section, summary, time, mark, audio, video {
  margin: 0;
  padding: 0;
  border: 0;
}

/* BODY
=============================================================================*/

body {
  font-family: Helvetica, arial, freesans, clean, sans-serif;
  font-size: 14px;
  line-height: 1.6;
  color: #333;
  background-color: #fff;
  padding: 20px;
  max-width: 960px;
  margin: 0 auto;
}

body>*:first-child {
  margin-top: 0 !important;
}

body>*:last-child {
  margin-bottom: 0 !important;
}

/* BLOCKS
=============================================================================*/

p, blockquote, ul, ol, dl, table, pre {
  margin: 15px 0;
}

/* HEADERS
=============================================================================*/

h1, h2, h3, h4, h5, h6 {
  margin: 20px 0 10px;
  padding: 0;
  font-weight: bold;
  -webkit-font-smoothing: antialiased;
}

h1 tt, h1 code, h2 tt, h2 code, h3 tt, h3 code, h4 tt, h4 code, h5 tt, h5 code, h6 tt, h6 code {
  font-size: inherit;
}

h1 {
  font-size: 28px;
  color: #000;
}

h2 {
  font-size: 24px;
  border-bottom: 1px solid #ccc;
  color: #000;
}

h3 {
  font-size: 18px;
}

h4 {
  font-size: 16px;
}

h5 {
  font-size: 14px;
}

h6 {
  color: #777;
  font-size: 14px;
}

body>h2:first-child, body>h1:first-child, body>h1:first-child+h2, body>h3:first-child, body>h4:first-child, body>h5:first-child, body>h6:first-child {
  margin-top: 0;
  padding-top: 0;
}

a:first-child h1, a:first-child h2, a:first-child h3, a:first-child h4, a:first-child h5, a:first-child h6 {
  margin-top: 0;
  padding-top: 0;
}

h1+p, h2+p, h3+p, h4+p, h5+p, h6+p {
  margin-top: 10px;
}

/* LINKS
=============================================================================*/

a {
  color: #4183C4;
  text-decoration: none;
}

a:hover {
  text-decoration: underline;
}

/* LISTS
=============================================================================*/

ul, ol {
  padding-left: 30px;
}

ul li > :first-child, 
ol li > :first-child, 
ul li ul:first-of-type, 
ol li ol:first-of-type, 
ul li ol:first-of-type, 
ol li ul:first-of-type {
  margin-top: 0px;
}

ul ul, ul ol, ol ol, ol ul {
  margin-bottom: 0;
}

dl {
  padding: 0;
}

dl dt {
  font-size: 14px;
  font-weight: bold;
  font-style: italic;
  padding: 0;
  margin: 15px 0 5px;
}

dl dt:first-child {
  padding: 0;
}

dl dt>:first-child {
  margin-top: 0px;
}

dl dt>:last-child {
  margin-bottom: 0px;
}

dl dd {
  margin: 0 0 15px;
  padding: 0 15px;
}

dl dd>:first-child {
  margin-top: 0px;
}

dl dd>:last-child {
  margin-bottom: 0px;
}

/* CODE
=============================================================================*/

pre, code, tt {
  font-size: 12px;
  font-family: Consolas, "Liberation Mono", Courier, monospace;
}

code, tt {
  margin: 0 0px;
  padding: 0px 0px;
  white-space: nowrap;
  border: 1px solid #eaeaea;
  background-color: #f8f8f8;
  border-radius: 3px;
}

pre>code {
  margin: 0;
  padding: 0;
  white-space: pre;
  border: none;
  background: transparent;
}

pre {
  background-color: #f8f8f8;
  border: 1px solid #ccc;
  font-size: 13px;
  line-height: 19px;
  overflow: auto;
  padding: 6px 10px;
  border-radius: 3px;
}

pre code, pre tt {
  background-color: transparent;
  border: none;
}

kbd {
    -moz-border-bottom-colors: none;
    -moz-border-left-colors: none;
    -moz-border-right-colors: none;
    -moz-border-top-colors: none;
    background-color: #DDDDDD;
    background-image: linear-gradient(#F1F1F1, #DDDDDD);
    background-repeat: repeat-x;
    border-color: #DDDDDD #CCCCCC #CCCCCC #DDDDDD;
    border-image: none;
    border-radius: 2px 2px 2px 2px;
    border-style: solid;
    border-width: 1px;
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    line-height: 10px;
    padding: 1px 4px;
}

/* QUOTES
=============================================================================*/

blockquote {
  border-left: 4px solid #DDD;
  padding: 0 15px;
  color: #777;
}

blockquote>:first-child {
  margin-top: 0px;
}

blockquote>:last-child {
  margin-bottom: 0px;
}

/* HORIZONTAL RULES
=============================================================================*/

hr {
  clear: both;
  margin: 15px 0;
  height: 0px;
  overflow: hidden;
  border: none;
  background: transparent;
  border-bottom: 4px solid #ddd;
  padding: 0;
}

/* TABLES
=============================================================================*/

table th {
  font-weight: bold;
}

table th, table td {
  border: 1px solid #ccc;
  padding: 6px 13px;
}

table tr {
  border-top: 1px solid #ccc;
  background-color: #fff;
}

table tr:nth-child(2n) {
  background-color: #f8f8f8;
}

/* IMAGES
=============================================================================*/

img {
  max-width: 100%
}
</style>
</head>
<body>
<h1>MySQL优化</h1>
<hr />
<p><strong>说明：</strong>基于MySQL5.5  
</p>
<h2>常见优化参数说明</h2>
<p>&emsp;&emsp;<code>innodb_buffer_pool_size:</code>这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。 <br />
&emsp;&emsp;<code>innodb_log_file_size：</code>这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。 <br />
&emsp;&emsp;一开始就把<code>innodb_log_file_size</code>设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G。  <br />
&emsp;&emsp;<code>max_connections:</code>如果你经常看到<code>‘Too many connections'</code>错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。<code>max_connection</code>值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。   
</p>
<h3>InnoDB配置</h3>
<p>&emsp;&emsp;从MySQL 5.5版本开始，InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。 <br />
&emsp;&emsp;<code>innodb_file_per_table：</code>这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里<code>（innodb_file_per_table = OFF）</code> 或者为每张表的数据单独放在一个.ibd文件<code>（innodb_file_per_table = ON）</code>。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表（比如10k+）。 <br />
&emsp;&emsp;MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。 <br />
&emsp;&emsp;<code>innodb_flush_log_at_trx_commit：</code>默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。  <br />
&emsp;&emsp;<code>innodb_flush_method:</code> 这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT；否则，大多数情况下应将其设为fdatasync（默认值）。sysbench是一个可以帮助你决定这个选项的好工具。 <br />
&emsp;&emsp;<code>innodb_log_buffer_size:</code>这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看<code>Innodb_log_waits</code>状态变量，如果它不是0，增加<code>innodb_log_buffer_size</code>。  
</p>
<h3>其他设置</h3>
<p>&emsp;&emsp;<code>query_cache_size:</code> query cache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设置<code>query_cache_size = 0</code>（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这是如果你想停用它，那就得小心了。 <br />
&emsp;&emsp;<code>log_bin：</code>如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 <code>PURGE BINARY LOGS</code> 来清除旧文件，或者设置 <code>expire_logs_days</code>来指定过多少天日志将被自动清除。 <br />
&emsp;&emsp;记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。 <br />
&emsp;&emsp;<code>skip_name_resolve：</code>当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭<code>skip_name_resolve</code>选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中必须格外小心。  
</p>
<p><code>skip-locking</code>  <br />
#避免MySQL的外部锁定，减少出错几率增强稳定性。 <br />
<code>skip-name-resolve</code>  <br />
#禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ <br />
<code>back_log = 384</code>  <br />
#<code>back_log</code>参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。 试图设定back<em>log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 <br />
<code>key_buffer_size = 256M</code> <br />
#<code>key_buffer_size</code>key</em>buffer_size指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值<code>Key_read_requests</code>和 <code>Key_reads</code>,可以知道<code>key_buffer_size</code>设置是否合理。比例<code>key_reads / key_read_requests</code>应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用<code>SHOW STATUS LIKE ‘key_read%'</code>获得)。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！  
</p>
<p><code>max_allowed_packet = 4M</code> <br />
增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。   
</p>
<p><code>thread_stack = 256K</code>    
</p>
<p><code>table_cache = 128K</code> <br />
<code>table_cache</code>指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值<code>Open_tables</code>和<code>Opened_tables</code>，可以决定是否需要增加<code>table_cache</code>的值。如果你发现 <code>open_tables</code>等于<code>table_cache</code>，并且<code>opened_tables</code>在不断增长，那么你就需要增加<code>table_cache</code>的值了(上述状态值可以使用<code>SHOW STATUS LIKE ‘Open%tables'</code>获得)。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。  <br />
<code>sort_buffer_size = 6M</code> <br />
#查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。</p>
<p><code>read_buffer_size = 4M</code>  <br />
#读查询操作所能使用的缓冲区大小。和<code>sort_buffer_size</code>一样，该参数对应的分配内存也是每连接独享。 <br />
<code>join_buffer_size = 8M</code>  <br />
#联合查询操作所能使用的缓冲区大小，和<code>sort_buffer_size</code>一样，该参数对应的分配内存也是每连接独享。  
</p>
<p><code>myisam_sort_buffer_size = 64M</code>  <br />
MyISAM表发生变化时重新排序所需的缓冲 <br />
<code>table_cache = 512</code> <br />
<code>table_cache</code>指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值<code>Open_tables</code>和<code>Opened_tables</code>，可以决定是否需要增加<code>table_cache</code>的值。如果你发现 <code>open_tables</code>等于<code>table_cache</code>，并且<code>opened_tables</code>在不断增长，那么你就需要增加<code>table_cache</code>的值了(上述状态值可以使用<code>SHOW STATUS LIKE ‘Open%tables'</code>获得)。注意，不能盲目地把<code>table_cache</code>设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。 
    thread<em>cache</em>size = 64 
    query<em>cache</em>size = 64M   
</p>
<p>#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，<code># &gt; SHOW VARIABLES LIKE ‘%query_cache%';</code> <code># &gt; SHOW STATUS LIKE ‘Qcache%';</code> 如果<code>Qcache_lowmem_prunes</code>的值非常大，则表明经常出现缓冲不够的情况；如果<code>Qcache_hits</code>的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；   <code>Qcache_free_blocks</code>，如果该值非常大，则表明缓冲区中碎片很多。 <br />
<code>tmp_table_size = 256M</code> <br />
<code>max_connections = 768</code>  <br />
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。 <br />
<code>max_connect_errors = 10000000</code>
对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST;。</p>
<p><code>wait_timeout = 10</code> <br />
#指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。 </p>
<p><code>thread_concurrency = 8</code><br />
#该参数取值为服务器逻辑CPU数量<em>2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4</em>2=8  
</p>
<p><code>skip-networking</code>    <br />
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！    
</p>
<p><code>table_cache=1024</code>  <br />
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳  <br />
<code>innodb_additional_mem_pool_size=4M</code>  <br />
#默认为2M  <br />
<code>innodb_flush_log_at_trx_commit=1</code>  <br />
#设置为0就是等到<code>innodb_log_buffer_size</code>列队满后再统一储存,默认为1  <br />
<code>innodb_log_buffer_size=2M</code> <br />
#默认为1M   <br />
<code>innodb_thread_concurrency=8</code> <br />
#你的服务器CPU有几个就设置为几,建议用默认一般为8  <br />
<code>key_buffer_size=256M</code><br />
#默认为218，调到128最佳 <br />
<code>tmp_table_size=64M</code>  <br />
#默认为16M，调到64-256最挂 <br />
<code>read_buffer_size=4M</code> <br />
#默认为64K  <br />
<code>read_rnd_buffer_size=16M</code>  <br />
#默认为256K  <br />
<code>sort_buffer_size=32M</code> <br />
#默认为256K  <br />
<code>thread_cache_size=120</code> <br />
#默认为60 <br />
<code>query_cache_size=32M</code>   
</p>
<h2>※值得注意的是：</h2>
<p>很多情况需要具体情况具体分析 <br />
一、如果<code>Key_reads</code>太大，则应该把my.cnf中<code>Key_buffer_size</code>变大，保持<code>Key_reads/Key_read_requests</code>至少1/100以上，越小越好。   
</p>
<h3>使用show status命令</h3>
<p>含义如下:
<code>aborted_clients</code> 客户端非法中断连接次数 <br />
<code>aborted_connects</code> 连接mysql失败次数 <br />
<code>com_xxx xxx</code>命令执行次数,有很多条 <br />
<code>connections</code> 连接mysql的数量 <br />
<code>Created_tmp_disk_tables</code> 在磁盘上创建的临时表  <br />
<code>Created_tmp_tables</code> 在内存里创建的临时表 <br />
<code>Created_tmp_files</code> 临时文件数  <br />
<code>Key_read_requests The number of requests to read a key block from the cache</code> <br />
<code>Key_reads The number of physical reads of a key block from disk</code> <br />
<code>Max_used_connections</code> 同时使用的连接数 <br />
<code>Open_tables</code> 开放的表 <br />
<code>Open_files</code> 开放的文件 <br />
<code>Opened_tables</code> 打开的表 <br />
<code>Questions</code> 提交到server的查询数 <br />
<code>Sort_merge_passes</code> 如果这个值很大,应该增加my.cnf中的sort_buffer值 <br />
<code>Uptime</code> 服务器已经工作的秒数   
</p>
<h3>提升性能的建议:</h3>
<p>1.如果<code>opened_tables</code>太大,应该把my.cnf中的<code>table_cache</code>变大 <br />
2.如果<code>Key_reads</code>太大,则应该把my.cnf中<code>key_buffer_size</code>变大.可以用<code>Key_reads/Key_read_requests</code>计算出cache失败率 <br />
3.如果<code>Handler_read_rnd</code>太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥索引的键的作用 <br />
4.如果<code>Threads_created</code>太大,就要增加my.cnf中<code>thread_cache_size</code>的值.可以用<code>Threads_created/Connections</code>计算cache命中率<br />
5.如果<code>Created_tmp_disk_tables</code>太大,就要增加my.cnf中<code>tmp_table_size</code>的值,用基于内存的临时表代替基于磁盘的    
</p>

</body>
</html>

